﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;

namespace MySQLAccess
{
    public class WeiboDB
    {
        static SQL sql = new SQL();
        public static UserDetail getUser(int user_id)
        {
            UserDetail user = new UserDetail();
            string strSQL = "select * from USER where USER_ID=" + user_id;
            
            DataTable dt=sql.RunSQL(strSQL);

            DataRow tr = dt.Rows[0];
            user.user_id = int.Parse(tr["USER_ID"].ToString());
            user.user_name=tr["USER_NAME"].ToString();
            return user;
        }
        public static int getCommentCount(int weibo_id)
        {
            int count = 0;
            string strSQL = "select count(*) as COUNT from COMMENT where  WEIBO_ID=" + weibo_id;
            DataTable dt=sql.RunSQL(strSQL);
            count = int.Parse(dt.Rows[0]["COUNT"].ToString());
            return count;
        }
        public static int getForwardCount(int weibo_id)
        {
            int count = 0;
            string strSQL = "select count(*) as COUNT from WEIBO_RAW where FORWARD_ID=" + weibo_id;
            DataTable dt = sql.RunSQL(strSQL);
            count = int.Parse(dt.Rows[0]["COUNT"].ToString());
            return count;
        }
        public static WeiboList getLatestWeibo(int num)
        {
            DataTable dt = sql.RunSQL("select * from WEIBO_RAW limit " + num);

            WeiboList list = new WeiboList();

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                WeiboDetail detail = new WeiboDetail();
                DataRow tr = dt.Rows[i];
                detail.weibo_id = int.Parse(tr["weibo_id"].ToString());
                detail.user_id = int.Parse(tr["USER_ID"].ToString());
                detail.content = tr["CONTENT"].ToString();
                detail.pub_time = tr["CREATED_AT"].ToString();
                detail.type = tr["type"].ToString();
                detail.like_count = int.Parse(tr["like_count"].ToString());


                detail.mid_pic = tr["BMIDDLE_PIC"].ToString();
                detail.small_pic = tr["THUMBNAIL_PIC"].ToString();
                detail.origin_pic = tr["ORIGINAL_PIC"].ToString();

                UserDetail user = WeiboDB.getUser(detail.user_id);
                detail.user_name = user.user_name;
                detail.comment_count = WeiboDB.getCommentCount(detail.weibo_id);
                detail.forward_count = WeiboDB.getForwardCount(detail.weibo_id);

                list.weibo_list.Add(detail);
            }
            return list;
        }
        public static WeiboComments getWeiboComments(int weibo_id)
        {
            WeiboComments comments=new WeiboComments();
            DataTable dt = sql.RunSQL("select * from COMMENT natural join USER where WEIBO_ID=" + weibo_id);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DataRow dr = dt.Rows[i];
                Comment comment = new Comment();
                comment.user_id = int.Parse(dr["USER_ID"].ToString());
                comment.user_name = dr["USER_NAME"].ToString();
                comment.comment_text = dr["COMMENT_TEXT"].ToString();
                comment.comment_time = dr["COMMENT_TIME"].ToString();

                comments.weibo_comments.Add(comment);

            }
            return comments;
        }
    }
}